USE [wdms]
GO

-- =============================================
-- Author     :		Lohith Ram D V
-- Create date:         08/mar/2011
-- =============================================

create procedure sp_GetDocumentsToCheck(
	@par_userid int)
	
AS
BEGIN
	DECLARE @roleid int
	DECLARE @rightsid int
	DECLARE @deptid int
	
	SELECT @roleid = (SELECT [User1].[role_id]
	FROM [User1]
	WHERE [User1].[user1_id]=@par_userid);
	
    SELECT @deptid = (SELECT [User1_Dept].[dept_id]
	FROM  [User1_Dept]
	WHERE @par_userid=[User1_Dept].[user1_id]);
	--Temp and System Administrator
	if(@roleid = 1 OR @roleid = 3) RETURN;
	
	--Guest
	if(@roleid=2)
	BEGIN
		SELECT [Document].[doc_title], [Doc_Share].[doc_id]
		FROM [Document], [Doc_Share]
		WHERE [Doc_Share].[user_target_id]=@par_userid AND [Document].[doc_id]=[Doc_Share].[doc_id] AND [Doc_Share].[sh_check]=1
	END
	
	--Employee
	if(@roleid=4)
	BEGIN
		SELECT [Document].[doc_title], [Document].[doc_id]
		FROM [Document]
		WHERE [Document].[user1_id]=@par_userid
		UNION
		SELECT [Document].[doc_title], [Doc_Share].[doc_id]
		FROM [Document], [Doc_Share]
		WHERE [Doc_Share].[user_target_id]=@par_userid AND [Document].[doc_id]=[Doc_Share].[doc_id] AND [Doc_Share].[sh_check]=1
	END
	
	if(@roleid=5)
	BEGIN
		SELECT [Document].[doc_title], [Document].[doc_id]
		FROM [Document], [Doc_Dept]
		WHERE [Document].[user1_id]=@par_userid
		UNION
		SELECT [Document].[doc_title], [Doc_Share].[doc_id]
		FROM [Document], [Doc_Share]
		WHERE [Doc_Share].[user_target_id]=@par_userid AND [Document].[doc_id]=[Doc_Share].[doc_id] AND [Doc_Share].[sh_check]=1
		UNION
		SELECT [Document].[doc_title], [Document].[doc_id]
		FROM [Document], [Doc_Dept]
		WHERE [Doc_Dept].[dept_id]=@deptid AND [Document].[doc_id]=[Doc_Dept].[doc_id]
	END
	
	if(@roleid=6)
	BEGIN
		SELECT [Document].[doc_title], [Document].[doc_id]
		FROM [Document]
	END
	
	
END
